package br.gen.seam.sqlserver.extract.tables;

import br.gen.seam.util.entity.Schema;
import br.gen.seam.util.entity.Table;
import br.gen.seam.util.jdbc.AbstractConnection;
import br.gen.seam.util.jdbc.exception.JDBCException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Diego Araujo | diegogomesaraujo@gmail.com
 * @version 1.0
 */
public class ExtractTables {

    private String database;
    
    private List<Schema> schemas;

    private AbstractConnection connection;

    public ExtractTables(String database, List<Schema> schemas) {
        this.database = database;
        this.schemas = schemas;
        connection = AbstractConnection.getInstance();
    }

    private void selectTables(Schema schema) throws SqlServerExtractTableException {
        try {
            connection.execute("SELECT * "
                    + "FROM INFORMATION_SCHEMA.TABLES "
                    + "WHERE TABLE_CATALOG='" + database + "' "
                    + "   AND TABLE_SCHEMA='" + schema.getName() + "' "
                    + "ORDER BY TABLE_SCHEMA, TABLE_NAME");
        } catch (JDBCException ex) {
            throw new SqlServerExtractTableException("Error when extracting tables: " + ex.getMessage(), ex);
        }
    }

    private void extractTables() throws SqlServerExtractTableException {
        try {
            connection.openConnection();
            for (int i = 0; i < schemas.size(); i++) {
                Schema schema = schemas.get(i);
                selectTables(schema);
                ResultSet resultSet = connection.getResultSet();

                List<Table> tables = new ArrayList<Table>();
                while(resultSet.next()) {
                    Table table = new Table();
                    table.setSchema(schema);
                    table.setName(resultSet.getString("TABLE_NAME"));
                    tables.add(table);
                }
                schema.setTables(tables);
                schemas.set(i, schema);
            }
            connection.closeConnection();
        } catch (JDBCException ex) {
            throw new SqlServerExtractTableException("Error when extracting tables: " + ex.getMessage(), ex);
        } catch (SQLException ex) {
            throw new SqlServerExtractTableException("Error when extracting tables: " + ex.getMessage(), ex);
        }
    }

    public List<Schema> getSchemas() throws SqlServerExtractTableException {
        extractTables();
        return schemas;
    }
}
